package cn.com.xuhx.testMapper;

import cn.com.xuhx.mapper.UserMapper;
import cn.com.xuhx.pojo.User;
import cn.com.xuhx.pojo.UserExample;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class SelectTest {

    private SqlSession sqlSession = null;
    private UserMapper mapper = null;

    @Before
    public void getSQLSession() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //设置需要手动提交事务
        //sqlSession = build.openSession();
        //设置自动提交事务
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @After
    public void closeSQLSession(){
        sqlSession.clearCache();
        sqlSession.close();
    }

    /**
     * 查询所有
     */
    @Test
    public void queryAll(){
        //select id, name, age, address, tel, sfzh, bornDay from T_USERS
        List<User> users = mapper.selectByExample(null);
        System.out.println(users);
    }

    /**
     * 根据ID查询数据
     */
    @Test
    public void queryByID(){
        //select id, name, age, address, tel, sfzh, bornDay from T_USERS where id = ?
        User user = mapper.selectByPrimaryKey(7);
        System.out.println(user);
    }

    /**
     *
     * example里的方法
     *  Like：模糊查询
     *  NotLike
     *  EqualTo：精确查询
     *  NotEqualTo
     *  Between：between ... and查询（包含边界）
     *  NotBetween
     *  GreaterThan：大于
     *  GreaterThanOrEqualTo：大于等于
     *  LessThan：小于
     *  LessThanOrEqualTo：小于等于
     *  In：in查询
     *  NotIn
     *  IsNotNull：非空查询
     *  IsNull：空值查询
     */
    @Test
    public void query(){
        //select id, name, age, address, tel, sfzh, bornDay from T_USERS WHERE ( name like ? )
        UserExample userExample = new UserExample();
        userExample.createCriteria().andNameLike("张%");
        List<User> users = mapper.selectByExample(userExample);
        System.out.println(users);
    }

    /**
     * or查询（在generator中，不支持 (A = ? or B = ?) and C = ?的逻辑 ）
     */
    @Test
    public void queryOR(){
        //select id, name, age, address, tel, sfzh, bornDay from T_USERS WHERE ( age >= ? and age <= ? ) or( id = ? )
        UserExample userExample = new UserExample();
        //userExample.createCriteria().andAgeGreaterThanOrEqualTo(20).andAgeLessThanOrEqualTo(23);
        userExample.or().andAgeGreaterThanOrEqualTo(20).andAgeLessThanOrEqualTo(23);
        userExample.or().andIdEqualTo(10);
        List<User> users = mapper.selectByExample(userExample);
        System.out.println(users);


    }
}
